﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Slam.Data
{
	internal static class Queries
	{
		public static string AddTableColumn
		{
			get
			{
				return @"begin
							CREATE TABLE [{0}].[{1}]}]) on delete cascade
							(
								[{3}] {4} NOT NULL,
								[{6}] {7} NOT NULL,
							 CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED 
							(
								[{3}] ASC,
								[{6}] ASC
							)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
							) ON [PRIMARY]

							ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{2}] FOREIGN KEY([{3}])
							REFERENCES [{0}].[{2}] ([{8}]) on delete cascade

							ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{2}]

							ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{5}] FOREIGN KEY([{6}])
							REFERENCES [{0}].[{5}] ([{9
							ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{5}]
						end";
			}
		}

		public static string ClearTable
		{
			get 
			{
				return @"delete from [{0}].[{1}]";
			}
		}

		public static string CreateAssociationTable
		{
			get
			{
				return @"begin
						CREATE TABLE [{0}].[{1}](
							[{3}] {4} NOT NULL,
							[{6}] {7} NOT NULL,
						 CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED 
						(
							[{3}] ASC,
							[{6}] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{2}] FOREIGN KEY([{3}])
						REFERENCES [{0}].[{2}] ([{8}]) on delete cascade

						ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{2}]

						ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{5}] FOREIGN KEY([{6}])
						REFERENCES [{0}].[{5}] ([{9}]) on delete cascade

						ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{5}]
						end";
			}
		}

		public static string CreateContentTable
		{
			get
			{
				return @"begin
							CREATE TABLE [{0}].[{1}](	
								{2}
							 CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED 
							(
								[{3}] ASC
							)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
							) ON [PRIMARY]

							ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_List] FOREIGN KEY([ListID])
							REFERENCES [SLAM].[List] ([ListID])

							ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_List]

							{4}
						end";
			}
		}

		public static string CreateSchema
		{ 
			get
			{
				return @"CREATE SCHEMA [{0}]";
			}
		}

		public static string CreateEventLogTable
		{
			get
			{
				return @"CREATE TABLE [{0}].[EventLog](
							[EventLogID] [int] IDENTITY(1,1) NOT NULL,
							[EventDate] [datetime] NULL,
							[EventName] [varchar](1000) NULL,
							[TypeName] [varchar](200) NULL,
							[Message] [varchar](max) NULL
						 CONSTRAINT [PK_SLAM_EventLog] PRIMARY KEY CLUSTERED 
						(
							[EventLogID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]";
			}
		}

		public static string CreateMessageLogTable
		{
			get
			{
				return @"CREATE TABLE [{0}].[MessageLog](
							[MessageLogID] [int] IDENTITY(1,1) NOT NULL,
							[MessageDate] [datetime] NULL,
							[MessageType] [varchar](1000) NULL,
							[Message] [varchar](max) NULL
						 CONSTRAINT [PK_SLAM_MessageLog] PRIMARY KEY CLUSTERED 
						(
							[MessageLogID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]";
			}
		}

		public static string CreateItemPropertyTable
		{
			get
			{
				return @"CREATE TABLE [SLAM].[ItemProperty](
							[ItemPropertyId] [int] IDENTITY(1,1) NOT NULL,
							[ListRelativeId] [int] NOT NULL,
							[ListID] [uniqueidentifier] NOT NULL,
							[Name] [varchar](255) NOT NULL,
							[Value] [varchar](max) NOT NULL,
						 CONSTRAINT [PK_ItemProperty] PRIMARY KEY CLUSTERED 
						(
							[ItemPropertyId] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						ALTER TABLE [SLAM].[ItemProperty]  WITH CHECK ADD  CONSTRAINT [FK_SLAM_ItemProperty_List] FOREIGN KEY([ListID])
						REFERENCES [SLAM].[List] ([ListID])

						ALTER TABLE [SLAM].[ItemProperty] CHECK CONSTRAINT [FK_SLAM_ItemProperty_List]

						CREATE NONCLUSTERED INDEX [IX_ListID] ON [SLAM].[ItemProperty] 
						(
							[ListRelativeId] ASC,
							[ListID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
			}
		}

		public static string DeleteAllAssociations
		{
			get
			{
				return @"delete from [{0}].[{1}] where {2} = '{3}'";
			}
		}

		public static string DeleteAssociation
		{
			get 
			{
				return @"delete from [{0}].[{1}] where {2} = '{3}' and {4} = '{5}'";
			}
		}

		public static string DeleteItem
		{
			get
			{
				return @"delete from [{0}].[{1}] where {3} = '{2}'";
			}
		}

		public static string InsertAssociation
		{
			get
			{
				return @"begin
								if not exists(select 1 from [{0}].[{1}] where [{2}] = '{4}' and [{3}] = '{5}')
								insert into [{0}].[{1}] ([{2}], [{3}]) values ('{4}', '{5}')
						end";
			}
		}

		public static string InsertItem
		{
			get 
			{
				return @"insert into [{0}].[{1}] ({2}) values ({3})";
			}
		}

		public static string RetreiveOrderedColumnsNamesForTable
		{
			get
			{
				return @"select	c.column_name as Name, c.data_type as DataType
						FROM	information_schema.columns c                
						WHERE (c.table_schema + '.' +  c.table_name) = '{0}.{1}'";
			}
		}

		public static string UpdateItem
		{
			get
			{
				return @"update [{0}].[{1}] set {2} where {4} = '{3}'";
			}
		}

		public static string VerifySchemaExists
		{
			get
			{
				return @"select count(*) from information_schema.schemata where schema_name = '{0}'";
			}
		}

		public static string VerifyTableExists
		{
			get 
			{
				return @"select	count(*)
						FROM	information_schema.tables
						where	table_schema = '{0}' and
								table_name = '{1}'";
			}
		}

		public static string AddIsSynchedColumn
		{
			get 
			{
				return @"begin
							if NOT EXISTS(
							select	1
							FROM	information_schema.columns c                
							WHERE (c.table_schema + '.' +  c.table_name) = '{0}.{1}' and
							c.column_name = 'SlamIsSynched')
							ALTER TABLE [{0}].[{1}] ADD
								SlamIsSynched bit NOT NULL CONSTRAINT DF_{1}_IsSynched DEFAULT 0
						end";
			}
		}

		public static string AddSynchUpdateAssociationTrigger
		{
			get
			{
				return @"CREATE TRIGGER [{0}].{1}Synched 
						   ON  [{0}].[{1}] 
						   AFTER INSERT, UPDATE
						AS 
						BEGIN

							update a 
							set	a.SlamIsSynched = 1
							from [{0}].[{1}] a, inserted i
							where	a.[{2}] = i.[{2}] and
						a.[{3}] = i.[{3}]

						END";
			}
		}

		public static string AddSynchUpdateTrigger
		{
			get 
			{
				return @"CREATE TRIGGER [{0}].{1}Synched 
						   ON  [{0}].[{1}] 
						   AFTER INSERT, UPDATE
						AS 
						BEGIN

						if (select count(*) from inserted) = 1
							update	[{0}].[{1}]
							set		SlamIsSynched = 1
							where	[{2}] = (select [{2}] from inserted)

						END";
			}
		}

		public static string ClearNonSynchedData
		{
			get
			{
				return @"delete from [{0}].[{1}] where SlamIsSynched = 0";
			}
		}

		public static string InsertLogItem
		{
			get
			{
				return "insert into [{0}].[{1}] select {2}";
			}
		}

		public static string RemoveIsSynchedColumn
		{
			get
			{
				return @"begin
						ALTER TABLE [{0}].[{1}] DROP CONSTRAINT DF_{1}_IsSynched 
						ALTER TABLE [{0}].[{1}] DROP COLUMN SlamIsSynched 
						end";
			}
		}

		public static string RemoveSynchUpdateTrigger
		{
			get
			{
				return @"DROP TRIGGER [{0}].[{1}Synched]";
			}
		}

		public static string VerifySynchUpdateTriggerExists
		{
			get
			{
				return @"SELECT count(*) FROM sys.triggers WHERE object_id = OBJECT_ID(N'{0}.{1}Synched')";
			}
		}

		public static string UpdateSingleTableColumn
		{
			get
			{
				return @"if NOT EXISTS(
						select	1
						FROM	information_schema.columns c                
						WHERE (c.table_schema + '.' +  c.table_name) = '{0}.{1}' and
						c.column_name = '{2}')
						ALTER TABLE [{0}].[{1}] ADD {3};";
			}
		}

		public static string CreateLocationTables
		{
			get 
			{
				return @"begin
						CREATE TABLE [SLAM].[SiteCollection](
							[SiteCollectionID] uniqueidentifier not null,
							[ServerRelativeUrl] varchar(260) not null,
						 CONSTRAINT [PK_SLAM_SiteCollection] PRIMARY KEY CLUSTERED 
						(
							[SiteCollectionID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						CREATE TABLE [SLAM].[Site](
							[SiteID] uniqueidentifier not null,
							[Name] varchar(260) not null,
							[Title] varchar(260) not null,
							[Url] varchar(260) not null,
							[SiteCollectionID] uniqueidentifier NOT NULL,
						 CONSTRAINT [PK_SLAM_Site] PRIMARY KEY CLUSTERED 
						(
							[SiteID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						CREATE TABLE [SLAM].[List](
							[ListID] uniqueidentifier not null,
							[DefaultViewUrl] varchar(260) not null,
							[NewFormUrl] varchar(260) not null,
							[EditFormUrl] varchar(260) not null,
							[DisplayFormUrl] varchar(260) not null,
							[SiteID] uniqueidentifier not null,
						 CONSTRAINT [PK_SLAM_List] PRIMARY KEY CLUSTERED 
						(
							[ListID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						ALTER TABLE [SLAM].[Site]  WITH CHECK ADD  CONSTRAINT [FK_Site_SiteCollection] FOREIGN KEY([SiteCollectionID])
						REFERENCES [SLAM].[SiteCollection] ([SiteCollectionID]) on delete cascade

						ALTER TABLE [SLAM].[Site] CHECK CONSTRAINT [FK_Site_SiteCollection]

						ALTER TABLE [SLAM].[List]  WITH CHECK ADD  CONSTRAINT [FK_List_Site] FOREIGN KEY([SiteID])
						REFERENCES [SLAM].[Site] ([SiteID]) on delete cascade

						ALTER TABLE [SLAM].[List] CHECK CONSTRAINT [FK_List_Site]

						end";
			}
		}

		public static string UpdateLocation
		{
			get 
			{
				return @"update	[SLAM].[SiteCollection]
						set		[ServerRelativeUrl] = '{1}'
						where	[SiteCollectionID] = '{0}'

						if @@ROWCOUNT < 1
							insert into [SLAM].[SiteCollection]
							select '{0}', '{1}'

						update	[SLAM].[Site]
						set		[Name] = '{3}',
														[Title] = '{4}',
								[Url] = '{5}',
								[SiteCollectionID] = '{0}'
						where	[SiteID] = '{2}'

						if @@ROWCOUNT < 1
							insert into [SLAM].[Site] (SiteID, Name, Title, Url, SiteCollectionID)
							values ('{2}', '{3}', '{4}', '{5}', '{0}')

						update	[SLAM].[List]
						set		[DefaultViewUrl] = '{7}',
								[NewFormUrl] = '{8}',
								[EditFormUrl] = '{9}',
								[DisplayFormUrl] = '{10}',
								[SiteID] = '{2}'
						where	[ListID] = '{6}'

						if @@ROWCOUNT < 1
							insert into [SLAM].[List]
							select '{6}', '{7}', '{8}', '{9}', '{10}', '{2}'";
			}
		}

		public static string CreateContentTableConstraint
		{
			get 
			{	
				return @"ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{2}] FOREIGN KEY([{3}])
						REFERENCES [{0}].[{2}] ([{4}]) on delete cascade

						ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{2}]";
			}
		}

		public static string AddSynchUpdateAttachmentTrigger
		{
			get
			{
				return @"CREATE TRIGGER [{0}].{1}Synched 
						   ON  [{0}].[{1}] 
						   AFTER INSERT, UPDATE
						AS 
						BEGIN

						if (select count(*) from inserted) = 1
							update	[{0}].[{1}]
							set		SlamIsSynched = 1
							where	[{2}] = (select [{2}] from inserted) and
						FileUrl = (select FileUrl from inserted)

						END";
			}
		}

		public static string CreateAttachmentTable
		{
			get
			{
				return @"CREATE TABLE [{0}].[{1}](
							[AttachmentID] [int] not null identity(1,1),
							[{3}] {4} not null,
							[FileUrl] [varchar](2000) not null
						 CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED 
						(
							[AttachmentID] ASC
						)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]

						ALTER TABLE [{0}].[{1}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{2}] FOREIGN KEY([{3}])
						REFERENCES [{0}].[{2}] ([{3}])
						ON DELETE CASCADE

						ALTER TABLE [{0}].[{1}] CHECK CONSTRAINT [FK_{1}_{2}]";
			}
		}
	}
}
